﻿//------------------------------------------------------------------------------------------------------
// <summary>
//     The following code is allowed to be used inernally in Pfizer China only, 
//     you are not permitted neither to use the contents nor disclose it in any
//     manner to third parties.
// </summary>
// <copyright file="SqlHelper.cs" company="Pfizer China">
//     Copyright (c) Pfizer China. All rights reserved.
// </copyright>
//------------------------------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using DAL;
using SystemFrameWork;

namespace DAL
{
    public abstract class SqlHelper
    {
        /// <summary>
        /// Hashtable to store cached parameters
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        private static SqlTransaction Transaction
        {
            get
            {
                DataContextManager mgr = new DataContextManager();
                return mgr.Transaction as SqlTransaction;
            }

            set
            {
                DataContextManager mgr = new DataContextManager();
                mgr.Transaction = value;
            }
        }

        public static SqlConnection GetConnection()
        {
            DataContextManager mgr = new DataContextManager();
            SqlConnection conn = mgr.GetConnection() as SqlConnection;

            if (mgr.Transaction != null)
            {
                Transaction = mgr.Transaction as SqlTransaction;
            }

            return conn;
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlTransaction trans, params SqlParameter[] commandParameters)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                SqlConnection conn = GetConnection();
                PrepareCommand(cmd, conn, GetTransaction(trans), cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public static DataSet StoreProcedureFill(string spName, params SqlParameter[] spParameters)
        {
            try
            {
                SqlConnection conn = GetConnection();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, Transaction, CommandType.StoredProcedure, spName, spParameters);
                SqlDataAdapter adaper = new SqlDataAdapter();
                adaper.SelectCommand = cmd;
                DataSet set = new DataSet();
                adaper.Fill(set);
                return set;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public static SqlTransaction BeginTransaction()
        {
            SqlConnection conn = GetConnection();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            Transaction = conn.BeginTransaction();
            return Transaction;
        }

        public static void CommitTransaction()
        {
            /// LogHelper.Log(LogLevel.Fatal, "Not Commit this transaction in SqlHelper");
            if (Transaction != null)
            {
                try
                {
                    Transaction.Commit();
                    Transaction = null;
                    ///LogHelper.Log(LogLevel.Fatal, "Commit this transaction in SqlHelper");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }

        public static void RollBack()
        {
            /// LogHelper.Log(LogLevel.Fatal, "Not RollBack this transaction in SqlHelper");
            if (Transaction != null)
            {
                try
                {
                    Transaction.Rollback();
                    Transaction = null;
                    ///LogHelper.Log(LogLevel.Fatal, "RollBack this transaction in SqlHelper");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }

        public static DataSet SelectData(string selectSQL)
        {
            try
            {
                SqlConnection conn = GetConnection();
                SqlCommand cmd = new SqlCommand();
                string sqlStatement = string.Empty;
                PrepareCommand(cmd, conn, Transaction, CommandType.Text, selectSQL, null);
                SqlDataAdapter adaper = new SqlDataAdapter();
                adaper.SelectCommand = cmd;
                DataSet set = new DataSet();
                adaper.Fill(set);
                return set;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public static DataSet SelectData(string selectSQL, SqlTransaction trans)
        {
            try
            {
                SqlConnection conn = GetConnection();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, GetTransaction(trans), CommandType.Text, selectSQL, null);
                SqlDataAdapter adaper = new SqlDataAdapter();
                adaper.SelectCommand = cmd;
                DataSet set = new DataSet();
                adaper.Fill(set);
                return set;
            }
            catch (Exception ex)
            {
                throw new  Exception(ex.Message, ex);
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            SqlConnection connection = GetConnection();
            DataSet dataSet = new DataSet();
            connection.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
            sqlDA.SelectCommand.CommandTimeout = 0;
            sqlDA.Fill(dataSet, tableName);
            connection.Close();
            return dataSet;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }


        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, GetTransaction(trans), cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = GetConnection();
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection connection = GetConnection();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        //omited by Li Xing 2009-12-21, the method below could not be compiled, to be continued
        //public static IQueryable<TEntity> Find<TEntity>(this IQueryable<TEntity> source, TEntity obj, bool isAnd) where TEntity : class
        //{
        //    if (source == null)
        //        throw new ArgumentNullException("Source can't be null!!");
        //    //获得所有property的信息
        //    PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

        //    Expression condition = null;
        //    //先构造了一个ParameterExpression对象，这里的c，就是Lambda表达中的参数。（c=>）  
        //    //本变量被移出了foreach循环
        //    ParameterExpression param = Expression.Parameter(typeof(TEntity), "c");
        //    //遍历每个property
        //    foreach (PropertyInfo p in properties)
        //    {
        //        if (p != null)
        //        {
        //            Type t = p.PropertyType;
        //            //只支持value型和string型的影射
        //            if (t.IsValueType || t == typeof(string))
        //            {
        //                //如果不为null才算做条件
        //                if (p.GetValue(obj, null) != null)
        //                {
        //                    //SQL Server does not support comparison of TEXT, NTEXT, XML and IMAGE ,etc
        //                    /**/
        //                    ///Only support BigInt,Bit,Char,Decimal,Money,NChar,Real,
        //                    ///Int,VarChar,SmallMoney,SmallInt,NVarChar,NVarChar(MAX),VarChar(MAX)
        //                    Attribute attr = Attribute.GetCustomAttribute(p, typeof(ColumnAttribute));
        //                    if (attr != null)
        //                    {
        //                        string dbType = (attr as ColumnAttribute).DbType;
        //                        if (dbType.Contains("Text") || dbType.Contains("NText")
        //                            || dbType.Contains("Xml") || dbType.Contains("Image")
        //                            || dbType.Contains("Binary") || dbType.Contains("DateTime")
        //                            || dbType.Contains("sql_variant") || dbType.Contains("rowversion")
        //                            || dbType.Contains("UniqueIdentifier") || dbType.Contains("VarBinary(MAX)"))
        //                        {
        //                            continue;
        //                        }
        //                    }
        //                    //构造表达式的右边，值的一边
        //                    Expression right = Expression.Constant(p.GetValue(obj, null), p.PropertyType);
        //                    //构造表达式的左边，property一端。
        //                    Expression left = Expression.Property(param, p.Name);
        //                    //生成筛选表达式。即c.CustomerID == "Tom"
        //                    Expression filter = Expression.Equal(left, right);
        //                    if (condition == null)
        //                    {
        //                        condition = filter;
        //                    }
        //                    else
        //                    {
        //                        if (isAnd)
        //                            condition = Expression.And(condition, filter);
        //                        else
        //                            condition = Expression.Or(condition, filter);
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    if (condition != null)
        //    {
        //        Expression<Func<TEntity, bool>> pred = Expression.Lambda<Func<TEntity, bool>>(condition, param);
        //        return source.Where(pred);
        //    }
        //    return source;

        //}

        public static IQueryable<T> DataSorting<T>(System.Linq.IQueryable<T> source, string sortExpression, string sortDirection)
        {
            string sortingDir = string.Empty;
            if (string.IsNullOrEmpty(sortDirection))
            {
                sortDirection = string.Empty;
            }
            if (sortDirection.ToUpper().Trim() == ConstantsManager.Desc)
            {
                sortingDir = "OrderByDescending";
            }
            else
            {
                sortingDir = "OrderBy";
            }

            ParameterExpression param = Expression.Parameter(typeof(T), sortExpression);
            PropertyInfo pi = typeof(T).GetProperty(sortExpression);
            Type[] types = new Type[2];
            types[0] = typeof(T);
            types[1] = pi.PropertyType;
            Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortExpression), param));
            IQueryable<T> query = source.AsQueryable().Provider.CreateQuery<T>(expr);
            return query;
        }

        private static SqlTransaction GetTransaction(SqlTransaction trans)
        {
            if (trans == null)
            {
                return Transaction;
            }
            else
            {
                return trans;
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;

            int timeOut = Int32.Parse(ConfigurationManager.AppSettings["ConnetionTimeOut"]);
            cmd.CommandTimeout = timeOut;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}
